#encoding: utf-8
=begin

Usage:      用于对比从吉客云倒出的库存与自有商店库存的差异
=end

require 'rest-client'
require 'json'
require 'os'
require 'open-uri'

load './rds_api.rb'
load './crmeb_utils.rb'

def resetStock mer_id
  inq = "UPDATE crmeb.eb_store_product_attr_value AS pav
LEFT JOIN crmeb.eb_store_product AS p ON pav.product_id = p.id
SET pav.stock = 0
WHERE
	pav.type <= 1
	AND p.mer_id = #{mer_id};"
  queryRds inq
  inq = "UPDATE crmeb.eb_store_product set stock = 0 where mer_id = #{mer_id};"
  queryRds inq
end

def getStoreStock mer_id
    inq = "select pav.stock as stock, `pav`.`unique` as unik, pav.bar_code as bar_code, p.store_name as name from crmeb.eb_store_product_attr_value AS pav
LEFT JOIN crmeb.eb_store_product AS p ON pav.product_id = p.id WHERE pav.bar_code != '' and p.mer_id = #{mer_id};"
    goods = {}
    res = queryRds inq
    res.each do |r|
        item = {"bar_code"=>r['bar_code'],"name"=>r['name'],"stock"=>r['stock'], "unik"=>r['unik']}
        goods.store r['bar_code'], item
    end
    return goods
end

def renameServerStockFile mer_id
  res = RestClient.get "https://mall.foodtrust.cn/api/rename_stock_file?mer_id=#{mer_id}"
end

def getServerStockFile mer_id

  fn = OS.windows? ? ".\\nginx\\html\\fc4-stocks\\final-stocks-M#{mer_id}-#{Time.now.strftime('%y%m%d')}.xlsx" : "./nginx/html/fc4-stocks/final-stocks-M#{mer_id}-#{Time.now.strftime('%y%m%d')}.xlsx"
  uri = "https://mall.foodtrust.cn/temp/final-stocks-M#{mer_id}-#{Time.now.strftime('%y%m%d')}.xlsx"

  #如果该文件不存在，所获内容是默认的404HTML
  data = open(uri, 'User-Agent' => 'ruby'){|f| f.read} 
  file = File.new fn, 'w+'
  file.binmode 
  file << data 
  file.flush 
  file.close

  return fn

end

def readJackyunStockFile xlsx

    goods = {}

    begin
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        updated_count = 0
        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            item = {}

            bar_code = s.cell(line,1)
            name = s.cell(line,2)
            stock = s.cell(line,31);

            if goods[bar_code].nil?
                item = { "bar_code"=>bar_code, "name"=>name, "stock"=>stock}
            else
                item = { "bar_code"=>bar_code, "name"=>name, "stock"=>stock+goods[bar_code]['stock']}
            end
            goods.store bar_code, item

            #puts "update product #{s.cell(line,1)} stock #{s.cell(line,7)} as unique #{s.cell(line,23)} or bar_code #{s.cell(line,3)} ...\r"
        end
    rescue => e
        puts "ERROR:#{e.to_s}"
    end

    return goods

end

def updateStock xlsx, mer_id

        resetStock mer_id
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        updated_count = 0
        s.first_row.upto(s.last_row) do |line|
            line_idx += 1
            next if line_idx == 1
            #print "update product #{s.cell(line,1)} stock #{s.cell(line,7)} as unique #{s.cell(line,23)} or bar_code #{s.cell(line,3)} ...\r"
            stock = s.cell(line,7).to_i
            if stock > 0
              updated_count += 1
              updateSingleProduct s, line, mer_id 
            end
        end
        puts "\ndone. #{line_idx} #{updated_count}"

end

def renameFile xlsx
        appendix = "_#{Time.now.strftime('%H%M%S')}.xlsx"
        new_file =  xlsx.gsub('.xlsx',appendix)
        File.rename xlsx, new_file
end

def updateSingleProduct s, line, mer_id

  stock = s.cell(line,7).to_i
  unique = s.cell(line,23)
  bar_code = s.cell(line, 3)

  inq = ''
  if unique.to_s != ''
    inq = "UPDATE crmeb.eb_store_product_attr_value AS pav
  LEFT JOIN crmeb.eb_store_product AS p ON pav.product_id = p.id
  SET pav.stock = #{stock}
  WHERE
    pav.unique = '#{unique}'
    AND pav.type <= 1
    AND p.mer_id = #{mer_id};"
  else
     inq = "UPDATE crmeb.eb_store_product_attr_value AS pav
    LEFT JOIN crmeb.eb_store_product AS p ON pav.product_id = p.id
    SET pav.stock = #{stock}
    WHERE
      pav.bar_code = '#{bar_code}'
      AND pav.type <= 1
      AND p.mer_id = #{mer_id};"
  end
  queryRds inq 

end

fn="Jackyun0807.xlsx"
jackyun_goods = readJackyunStockFile fn
fcstore_goods = getStoreStock 1

puts "comparing stock between jackyun and foodtrust store"

synced = {}
unmatched = []
to_reset = []
alone = {} #不存在于fcstore
sqls = []

jackyun_goods.each do |bar_code, item|

    if fcstore_goods[bar_code].nil?
        #print"x"
        alone.store bar_code, item
    else
        fc_item = fcstore_goods[bar_code]
        if fc_item['stock'] == item['stock']
            #print "."
            synced.store bar_code, item
        else
            #print "u"
            rec = { "bar_code"=>item['bar_code'], 
                    "name"=>item['name'], 
                    "unik"=>fc_item["unik"],
                    "jackyun_stock"=>item['stock'], 
                    "fcstore_stock"=>fc_item['stock'] 
            }
            if (item['stock']==0) 
                to_reset += [ rec ]
                sql = "update crmeb.eb_store_product_attr_value set stock = 0 where `unique` = '#{rec['unik']}'; #previous stock = #{rec['fcstore_stock']} #{rec['name']} #{rec['bar_code']}"
                sqls += [sql]
            else
                unmatched += [ rec ]
            end
        end
    end
end
puts "no_present #{alone.size}"
puts "synced: #{synced.size}"

headers = ["bar_code","name","unik","jackyun_stock","fcstore_stock"]
names = ["bar_code","name","unik","jackyun_stock","fcstore_stock"]
fn = "to_reset_0807.xls"
fn= "unmatched_0807.xls"
puts "unmatched: #{unmatched.size}. write to #{fn}"
save_to_excel headers, names, unmatched, fn
fn= "zero_stock_0807.xls"
puts "zero_sotck: #{to_reset.size}. write to #{fn}"
save_to_excel headers, names, to_reset, fn
